﻿ using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Configuration;
using System.IO;
using System.Net;

namespace ProjectCommon
{
    public class ClassCommon
    {
        public DataTable dtLoginUser;
        public SqlConnection scKengee_IT_MIS;

        public enum OperateState { osNew = 1, osModify, osView };

        public static int CopyComboBoxItem(ComboBox cbSource, ComboBox cbDestion)
        {
            int result = 0;
            cbDestion.Items.Clear();
            for (int i = 0; i < cbSource.Items.Count; i++)
            {
                cbDestion.Items.Add(cbSource.Items[i].ToString());
            }
            return result;
        }

        public static string GetSQL_Insert(DataTable dtClass, string TableName)
        //生成插入的sql语句
        {
            string result = "INSERT INTO " + TableName + " \r\n (";
            for (int i = 0; i < dtClass.Columns.Count; i++)
            {
                if (dtClass.Columns[i].ColumnName.ToUpper().Equals("ID"))
                {
                    continue;
                }
                else if (i == (dtClass.Columns.Count - 1))
                {
                    result += "[" + dtClass.Columns[i].ColumnName + @"]) VALUES (";
                }
                else
                {
                    result += "[" + dtClass.Columns[i].ColumnName + "], \r\n";

                }
            }
            for (int i = 0; i < dtClass.Columns.Count; i++)
            {
                //ProjectCommon.ClassCommon.ToDBC((sender as TextBox).Text);
                if (dtClass.Columns[i].ColumnName.ToUpper().Equals("ID"))
                {
                    continue;
                }
                else if (i == (dtClass.Columns.Count - 1))
                {
                    result += "'" + dtClass.Rows[0][i].ToString() + @"')";
                }
                else
                {
                    result += "'" + dtClass.Rows[0][i].ToString() + "', \r\n";

                }
            }
            result = ToDBC(result);
            return result;
        }

        public static string GetSQL_Update(DataTable dtClass, string TableName)
        //生成修改的SQL语句
        {
            string result = "UPDATE " + TableName + " \r\n SET ";
            for (int i = 0; i < dtClass.Columns.Count; i++)
            {
                if (dtClass.Columns[i].ColumnName.ToUpper().Equals("ID"))
                {
                    continue;
                }
                else if (i == (dtClass.Columns.Count - 1))
                {
                    result += "[" + dtClass.Columns[i].ColumnName + "]='" + dtClass.Rows[0][i].ToString() + "' \r\n WHERE  ID='" + dtClass.Rows[0]["ID"].ToString() + @"'";
                }
                else
                {
                    result += "[" + dtClass.Columns[i].ColumnName + "]='" + dtClass.Rows[0][i].ToString() + "', \r\n";

                }
            }
            result = ToDBC(result);
            return result;
        }

        public static int ClassAppend(SqlConnection scn, DataTable dtClass, string TableName)
        //添加新的门店信息，返回值为影响行数，如果为1则成功，不为1则失败
        {
            int result = 0;
            string sql = ClassCommon.GetSQL_Insert(dtClass, TableName);
            SqlCommand scm = new SqlCommand(sql, scn);
            scn.Open();
            result = scm.ExecuteNonQuery();
            scn.Close();
            scm.Dispose();
            return result;
        }

        public static int ClassModify(SqlConnection scn, DataTable dtClass, string TableName)
        //门店信息修改，返回值为影响行数，如果为1则成功，不为1则失败
        {
            int result = 0;
            string sql = ClassCommon.GetSQL_Update(dtClass, TableName);
            SqlCommand scm = new SqlCommand(sql, scn);
            scn.Open();
            result = scm.ExecuteNonQuery();
            scn.Close();
            scm.Dispose();
            return result;
        }

        public static void SetForm2Class(DataTable dtClass, Control.ControlCollection FormControls)
        //填写窗体信息到类中
        {
            for (int i = 0; i < dtClass.Columns.Count; i++)
            {
                string FieldName = dtClass.Columns[i].ColumnName;
                if (FieldName.ToUpper().Equals("ID"))
                {
                    continue;
                }
                else
                {
                    Control[] ControlFinded = FormControls.Find(FieldName, true);
                    if (ControlFinded[0] is TextBox)
                    {
                        dtClass.Rows[0][i] = (ControlFinded[0] as TextBox).Text;
                    }
                    else if (ControlFinded[0] is ComboBox)
                    {
                        dtClass.Rows[0][i] = (ControlFinded[0] as ComboBox).Text;
                    }
                    else if (ControlFinded[0] is RichTextBox)
                    {
                        dtClass.Rows[0][i] = (ControlFinded[0] as RichTextBox).Text;
                    }
                }
            }
        }

        public static void SetClass2Form(DataTable dtClass, Control.ControlCollection FormControls)
        //填写类信息到窗体中
        {
            for (int i = 0; i < dtClass.Columns.Count; i++)
            {
                string FieldName = dtClass.Columns[i].ColumnName;
                if (FieldName.ToUpper().Equals("ID"))
                {
                    continue;
                }
                else
                {
                    Control[] ControlFinded = FormControls.Find(FieldName, true);
                    if (ControlFinded[0] is TextBox)
                    {
                        (ControlFinded[0] as TextBox).Text = dtClass.Rows[0][i].ToString();
                    }
                    else if (ControlFinded[0] is ComboBox)
                    {
                        (ControlFinded[0] as ComboBox).Text = dtClass.Rows[0][i].ToString();
                    }
                    else if (ControlFinded[0] is RichTextBox)
                    {
                        (ControlFinded[0] as RichTextBox).Text = dtClass.Rows[0][i].ToString();
                    }
                }
            }

            //csShop.dtShop.Rows[0]["ShopNo"] = tbShopNo.Text;
            //csShop.dtShop.Rows[0]["ShopName"] = tbShopName.Text;
            //csShop.dtShop.Rows[0]["ShopState"] = cbShopState.Text;
            //csShop.dtShop.Rows[0]["ShopLever"] = cbShopLever.Text;
            //csShop.dtShop.Rows[0]["Telphone"] = tbTelphone.Text;
            //csShop.dtShop.Rows[0]["ManagerPhone"] = tbManagerPhone.Text;
            //csShop.dtShop.Rows[0]["ForcePhone"] = tbForcePhone.Text;
            //csShop.dtShop.Rows[0]["CircuitTypes"] = cbCircuitTypes.Text;
            //csShop.dtShop.Rows[0]["SpecialGateway"] = tbSpecialGateway.Text;
            //csShop.dtShop.Rows[0]["CircuitNumber"] = tbCircuitNumber.Text; 
            //csShop.dtShop.Rows[0]["PartnerNumber"] = tbPartnerNumber.Text;
            //csShop.dtShop.Rows[0]["TerminalNumber"] = tbTerminalNumber.Text;
            //csShop.dtShop.Rows[0]["Port"] = tbPort.Text;
            //csShop.dtShop.Rows[0]["FastCode"] = tbFastCode.Text;
        }

        public static DataTable GetTableInfo(SqlConnection scn, string TableName)
        //获取表的字段名及期字段说明
        {
            string sql = @"SELECT
                            (case when a.colorder=1 then d.name else '' end) 表名,
                            a.colorder 字段序号,
                            a.name 字段名,
                            g.[value] AS 字段说明
                            FROM syscolumns a left join systypes b
                            on a.xtype=b.xusertype
                            inner join sysobjects d
                            on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
                            left join sys.extended_properties g
                            on a.id=g.major_id AND a.colid = g.minor_id
                            WHERE d.[name] ='"+TableName+@"' --你要查看的表名，注释掉，查看当前数据库所有表的字段信息
                            order by a.id,a.colorder";
            SqlDataAdapter sda = new SqlDataAdapter(sql, scn);
            DataTable dtTableInfo=new DataTable();
            scn.Open();
            sda.Fill(dtTableInfo);
            scn.Close();
            sda.Dispose();
            return dtTableInfo;
        }

        public static void SetdgvColumnName(SqlConnection scn, DataGridView dgv, string TableName)
        //设置字段说明为字段名
        {
            DataTable dtTableInfo = ProjectCommon.ClassCommon.GetTableInfo(scn, TableName);
            for (int i = 0; i < dtTableInfo.Rows.Count; i++)
            {
                string FieldName = dtTableInfo.Rows[i]["字段名"].ToString();
                string FieldDescription = dtTableInfo.Rows[i]["字段说明"].ToString();
                dgv.Columns[i].HeaderText = FieldDescription;
            }
        }
        public static void SetdgvColumnsHide(DataGridView dgv, string DisplayColumnName)
        //设置DataGridView中需要隐藏的列
        {
            for (int i = 0; i < dgv.ColumnCount; i++)
            {
                if (DisplayColumnName.Contains(dgv.Columns[i].HeaderText))
                {
                    dgv.Columns[i].Visible = false;
                }
                else
                {
                    dgv.Columns[i].Visible = true;
                }
            }
        }

        public static DataTable GetSelectTable(DataTable dtSource, string sSelect)
        //获取DataTable中SelectTable
        {
            DataTable dtSelectTable = new DataTable();
            dtSelectTable = dtSource.Clone();
            DataRow[] drtmp = dtSource.Select(sSelect);
            for (int i = 0; i < drtmp.Length; i++)
            {
                dtSelectTable.ImportRow(drtmp[i]);
            }
            return dtSelectTable;
        }

        /// 转全角的函数(SBC case)
        ///
        ///任意字符串
        ///全角字符串
        ///
        ///全角空格为12288，半角空格为32
        ///其他字符半角(33-126)与全角(65281-65374)的对应关系是：均相差65248
        ///
        public static String ToSBC(String input)
        {
            // 半角转全角：
            char[] c = input.ToCharArray();
            for (int i = 0; i < c.Length; i++)
            {
                if (c[i] == 32)
                {
                    c[i] = (char)12288;
                    continue;
                }
                if (c[i] < 127)
                    c[i] = (char)(c[i] + 65248);
            }
            return new String(c);
        }


        /// 转半角的函数(DBC case)
        ///
        ///任意字符串
        ///半角字符串
        ///
        ///全角空格为12288，半角空格为32
        ///其他字符半角(33-126)与全角(65281-65374)的对应关系是：均相差65248
        ///
        public static String ToDBC(String input)
        {
            char[] c = input.ToCharArray();
            for (int i = 0; i < c.Length; i++)
            {
                if (c[i] == 12288)
                {
                    c[i] = (char)32;
                    continue;
                }
                if (c[i] > 65280 && c[i] < 65375)
                    c[i] = (char)(c[i] - 65248);
            }
            return new String(c);
        }

        public static bool SetPowerRight(char cBool)
        {
            bool result = false;
            switch (cBool)
            {
                case '0': result = false; break;
                case '1': result = true; break;
            }
            return result;
        }

        ////保存文件，不仅是exe文件，所有的类型的文件操作都一样，我是保存一个txt文件
        //public void FileSave2DB(SqlConnection scn, string FileName)
        //{
        //    byte[] buffer;
        //    buffer = File.ReadAllBytes(FileName); //读取文件内容

        //    SqlCommand scm = scn.CreateCommand();
        //    scm.CommandText = "INSERT INTO ProgramFile (FileContent) VALUES (@FileContent)"; //FileContent字段是Image类型
        //    scm.Parameters.Add("@FileContent", SqlDbType.Image);
        //    scm.Parameters["@FileContent"].Value = buffer; //接受byte[]类型的值

        //    scn.Open();

        //    scm.ExecuteNonQuery();
        //    scn.Close();
        //    scm.Dispose();
        //}

        //public void FilePartition2DB(SqlConnection scn, string FileName)
        ////文件分割
        //{
            
        //}

        //public void FileOutput_MergeFromDB(SqlConnection aSqlConnection, string aFilePath)
        ////文件合并
        //{
        //    byte[] buffer = null;
        //    string FileName = System.IO.Path.GetFileName(aFilePath);
        //    SqlCommand scnFile = aSqlConnection.CreateCommand();
        //    scnFile.CommandText = @"SELECT count(*) FROM ProgramFile where FileName='" + FileName + "'";
        //    aSqlConnection.Open();
        //    int iCount = (int)scnFile.ExecuteScalar();
        //    for (int i = 0; i < iCount; i++)
        //    {
        //        scnFile.CommandText = "SELECT FileContent FROM ProgramFile where (FileName='" + FileName + "') and (Version='" + i.ToString() + "/" + iCount.ToString() + "')";

        //        buffer = (byte[])scnFile.ExecuteScalar(); //从数据库中取到的Image类型的字段的值，转换成byte[]类型
        //    }
        //    aSqlConnection.Close();
        //    File.WriteAllBytes(aFilePath, buffer);
        //}

        //private void FileOutput(SqlConnection scn, string aFilePath)
        ////输出文件，500K以下的文件
        //{
        //    byte[] buffer;
        //    string FileName = System.IO.Path.GetFileName(aFilePath);
        //    SqlCommand cmd = scKengee_IT_MIS.CreateCommand();
        //    cmd.CommandText = "SELECT FileContent FROM ProgramFile where FileName='" + FileName + "'";
        //    scKengee_IT_MIS.Open();
        //    buffer = (byte[])cmd.ExecuteScalar(); //从数据库中取到的Image类型的字段的值，转换成byte[]类型
        //    scKengee_IT_MIS.Close();
        //    File.WriteAllBytes(aFilePath, buffer);
        //}

        //public string md5_hash(string path)
        //{
        //    try
        //    {
        //        FileStream get_file = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);
        //        System.Security.Cryptography.MD5CryptoServiceProvider get_md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
        //        byte[] hash_byte = get_md5.ComputeHash(get_file);
        //        string resule = System.BitConverter.ToString(hash_byte);
        //        resule = resule.Replace("-", "");
        //        return resule;
        //    }
        //    catch (Exception e)
        //    {
        //        return e.ToString();
        //    }
        //}        
    }
}
